# coding: utf-8

from sqlalchemy import Table, String, Column, MetaData, Integer, FLOAT, Sequence, Date,DateTime, ForeignKey
from sqlalchemy.orm import mapper, sessionmaker, Session, relationship
from sqlalchemy import desc
from sqlalchemy.ext.declarative import declarative_base
import datetime
import dbconfig
from search import Page

Base = declarative_base()
Session = sessionmaker(bind = dbconfig.engine)
session = Session()

class Stock(Base):
    __tablename__ = 'stocks'
    __table_args__ =  {'mysql_engine':'InnoDB', 'mysql_charset':'utf8'}
    
    # we have to use String, because 000001 should not be trimed as 1
    id = Column(String(8), primary_key=True)
    name = Column(String(10))
    updated_time = Column(DateTime)
    
    def __init__(self, id, name):
        self.id = id
        self.name = name
        
    def __repr__(self):
        return self.name

class HolderQuarterReport(Base):
    __tablename__ = 'holder_quarter_reports'
    __table_args__ =  {'mysql_engine':'InnoDB', 'mysql_charset':'utf8'}
    
    id = Column(Integer, primary_key=True)
    stock_id = Column(String(8), ForeignKey('stocks.id'))
    trader_amount = Column(Integer)
    amount_change_rate =Column(FLOAT)
    amount_per_trader = Column(FLOAT)
    # tradable, untradable
    type = Column(Integer)
    date = Column(Date)
    released_date = Column(Date)
    
    stock = relationship('Stock')
    
    
    def __init__(self, stock_id=None, trader_amount=None, amount_per_trader=None, 
                 amount_change_rate=None, type=None,date=None, released_date=None):
        self.stock_id= stock_id
        self.trader_amount = trader_amount
        self.amount_per_trader = amount_per_trader
        self.amount_change_rate = amount_change_rate
        self.type = type
        self.date = date
        self.released_date = released_date

class TradableStockHolder(Base):
    __tablename__  = 'tradable_holders'
    __table_args__ =  {'mysql_engine':'InnoDB', 'mysql_charset':'utf8'}
    
    
    id = Column(Integer, primary_key=True)
    report_id = Column(Integer, ForeignKey('holder_quarter_reports.id'))
    holder_name = Column(String(255))
    amount = Column(Integer)
    percentage = Column(FLOAT)
    
    report = relationship('HolderQuarterReport')
    
    def __init__(self, report_id=None, holder_name=None, amount=None, percentage=None):
        self.report_id = report_id
        self.holder_name = holder_name
        self.amount = amount
        self.percentage = percentage
        
    def __repr__(self):
        return '%(holder_name)s buys %(amount)d' % \
                {"holder_name": self.holder_name, "amount": self.amount}
    

def getSesstion():
    return session

def create(obj):
    session.add(obj)
    session.commit()
    

metadata = Base.metadata
#metadata.create_all(dbconfig.engine)


#test2 = TradableStockHolder(600116, u"东安动力", "2010-10-23", "ivan", 300, 0.22)
#print(test2,unicode())
#create(test2)
#test1 = TradableStockHolder()
#a = session.query(TradableStockHolder).filter_by(id=600116).first()
#print(test1.stock_name)


def search_stock_with_pagination(page, size=50):
    total = session.query(Stock).count()
    offset = page * size
    result = session.query(Stock).limit(size).offset(offset)
         
    return Page(page, size, result, total)

def search_latest_quarter_holder(stock_id):
    result = session.query(HolderQuarterReport).filter(
        HolderQuarterReport.stock_id == stock_id).order_by(
            desc(HolderQuarterReport.date))
    return result.first()

def search_quarter_report(stock_id, date, _session=None):
    if not _session:
        _session = session
    result = session.query(HolderQuarterReport).filter(
        HolderQuarterReport.stock_id == stock_id).filter(
        HolderQuarterReport.date == date).first()
    return result


def test_search_latest_quarter_holder(stock_id):
    result = search_latest_quarter_holder(stock_id)
    print result.date

def test_create_stock_holder_quarter_report():
    # should be fail with foreign key constrait
    # stock_id = '1' 
    report = HolderQuarterReport(stock_id, 300, 23.4, 12, 1, None, None)
    create(report)

def test_create_tradable_holder():
    report_id = 1
    holder_name = 'test'
    amount = 400
    percentage = 0.55
    holder = TradableStockHolder(report_id, holder_name, amount, percentage)
    create(holder)
    print(HolderQuarterReport(holder.report).stock_id)

def test_search_tradable_holder():
    holder = session.query(TradableStockHolder).filter(TradableStockHolder.id==1).one()
    print(holder.report.stock_id)

def test_search_quarter_report():
    date = datetime.date(2011, 3, 31)
    report = search_quarter_report('000001', date)
    print report.date

if __name__ == '__main__':
    #test_search_tradable_holder()
    #test_search_latest_quarter_holder('002568')
    #test_search_quarter_report()
    pass